USE [OutdoorAgency]
GO
/****** Object:  View [dbo].[v_PropertyOfConstraction]    Script Date: 06/18/2009 21:43:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[v_PropertyOfConstraction] 
AS

SELECT  	
	Constraction.ID as ConstractionID,	
	T.c.value('(./@ParamID)[1]','Varchar(max)') as Param,	
	T.c.value('./@Value','varchar(max)')  as [Value],
	wdParam.Value as ParamTxt ,
	isnull(wdValue.Value,T.c.value('./@Value','varchar(max)')) as ValueTxt
FROM   
Constraction CROSS APPLY XML.nodes('*/SysPropertyList//DictionaryItem') T(c)
join
v_WordDictionary wdParam on  wdParam.Id=T.c.value('(./@ParamID)[1]','Varchar(max)')
left join
v_WordDictionary wdValue on  wdValue.Id=T.c.value('./@Value','varchar(max)')

UNION

SELECT 
	Constraction.ID as ConstractionID,
	T.c.value('fn:local-name(.)', 'varchar(max)')  as Param,
	T.c.value('.', 'varchar(max)')  as [Value],
	T.c.value('fn:local-name(.)', 'varchar(max)')  as ParamTxt,
	T.c.value('.', 'varchar(max)')  as [ValueTxt]
 FROM dbo.Constraction as Constraction
   CROSS APPLY XML.nodes('
for $b in /Constraction/@*
where fn:local-name($b) = "GID"
   or fn:local-name($b) = "DepartmentID" 
   or fn:local-name($b) = "Light"
  return $b
') as T(c)

